package com.supermap.agrinfo.dao;

import com.supermap.agrinfo.entity.Agrinfo;
import com.supermap.agrinfo.entity.AgrinfoComment;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

/**
 * @Author:langhongshuai
 * @Description:农情数据访问层
 * @Date:18/1/29 下午2:42
 */
@Mapper
public interface AgrinfoDao {
    /**
     * 获得用户的所有农情信息
     * @return
     */
    @Select("select t3.* ,t4.name as cropname from (select t1.*,t2.username from t_agrinfo t1 \n" +
            "inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "inner JOIN t_crop t4 on t3.type=t4.id where IF(ISNULL(#{userId}),true,t3.userId=#{userId}) and IF(ISNULL(#{areaCode}),true,t3.areacode like #{areaCode})")
    public List<Agrinfo> getAgrinfoByUIdArea(@Param("userId")String userId,@Param("areaCode")String areaCode);

    /**
     *增加农情
     * @param agrinfo
     */
    @Insert("insert into t_agrinfo(userId,farm,type,breed,growthperiod,longitude,latitude,areacode,descript,img_url)\n " +
            "values(#{agrinfo.userId},#{agrinfo.farm},#{agrinfo.type},#{agrinfo.breed},#{agrinfo.growthperiod},\n" +
            "#{agrinfo.longitude},#{agrinfo.latitude},#{agrinfo.areacode},#{agrinfo.descript},#{agrinfo.imgUrl})")
    public void addAgrinfo(@Param("agrinfo")Agrinfo agrinfo);

    /**
     * 获取某地区各分类在当年每个月农情上传的数量
     * @return
     */
    @Select("<script>\n" +
            "SELECT t.areacode,c.name,COUNT(t.id) AS count,date_format(t.uploadTime,'%m') AS month, date_format(t.uploadTime,'%Y') AS year\n" +
            "FROM t_agrinfo t JOIN t_cropstype c ON c.id = t.type WHERE 1 = 1\n" +
            "<if test='areaCodes != null'>\n" +
            "AND t.areacode IN\n" +
            "<foreach collection='areaCodes' open='(' close=')' item='areaCode' separator=','>\n" +
            "#{areaCode}\n" +
            "</foreach>\n" +
            "</if>\n" +
            "<if test='year != null'>\n" +
            "AND date_format(t.uploadTime,'%Y') = #{year}" +
            "</if>" +
            "AND c.levels = 2" +
            "GROUP BY\n" +
            "date_format(t.uploadTime,'%Y-%m'), t.type, t.areacode\n" +
            "</script>")
    List<Agrinfo> getAgriMsgCountByArea(@Param("areaCodes")String[] areaCodes, @Param("year")String year);

    /**
     * 获取所有的农情信息
     * @return
     */
    @Select("select t3.* ,t4.`name` as cropname from (select t1.*,t2.username from t_agrinfo t1 \n" +
            "Inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "Inner JOIN t_crop t4 on t3.type=t4.id;")
    public List<Agrinfo> getAllAgrinfo();

    /**
     * 获取所有的农情评论
     * @return
     */
    @Select("select * from t_agrinfocomment")
    public List<AgrinfoComment> getAllAgrinfoComment();

    /**
     * 得到农情评论根据农情ID
     * @param agrInfoId
     * @return
     */
    @Select("select t1.*,t2.username from t_agrinfocomment t1 INNER JOIN t_user t2 on t2.id=t1.userId  where t1.agrInfoId=#{agrInfoId}")
    List<AgrinfoComment> getAllAgrinfoCommentByagrInfoId(@Param("agrInfoId")int agrInfoId);
    /**
     * 添加农情评论内容
     * @param agrinfoComment
     * @return
     */
    @Insert("Insert into t_agrinfocomment(agrInfoId,comment,parentId,userId) "+"values(#{agrinfoComment.agrInfoId},#{agrinfoComment.comment},#{agrinfoComment.parentId},#{agrinfoComment.userId}"+")")
    int addagrinfocomment(@Param("agrinfoComment")AgrinfoComment agrinfoComment);

    /**
     * 添加默认为未阅读的评论
     * @param userid
     * @param typeid
     * @param type
     * @param comment
     * @param state
     * @return
     */
    @Insert("Insert into t_newmessage(userid,typeid,type,comment,state,uid) "
            +"values(#{userid},#{typeid},#{type},#{comment},#{state},#{uid})")
    int addNewMessage(@Param("userid")int userid, @Param("typeid")int typeid, @Param("type")int type, @Param("comment")String comment, @Param("state")int state,@Param("uid")String uid);

    /**
     * 删除农情
     * @param agriId
     */
    @Delete("Delete from t_agrinfo where id=#{agriId}")
    public void delAgrinfo(@Param("agriId")int agriId);

    /**
     * 根据areacode和type得到农情
     * @param areacode
     * @param type
     * @return
     */

    @Select("select * from t_agrinfo where areacode=#{areacode} and type=#{type} ")
    List<Agrinfo> getAgrinfoByAreacodeandCropid(@Param("areacode")String areacode,@Param("type")String type);

    /**
     * 根据条件得到农情
     * @param type
     * @return
     */
    @Select("select t1.*,t2.username as username,t3.img_ico as cropImgIcon,t4.areaname from t_agrinfo t1  left join t_user t2 on t1.userId = t2.id left join t_area t4 on t4.areacode = t1.areacode left join t_crop t3 on t1.type in(select id from t_crop where id in(select id from t_crop where parentid = t3.id or id=t3.id) or parentid in (select id from t_crop where parentid = t3.id )) "
             + " where if((isnull(#{type}) or#{type}=''),true,t1.type in(select id from t_crop where id in(select id from t_crop where parentid = #{type} or id= #{type}) or"
             + " parentid in (select id from t_crop where parentid = #{type}  ))) and if(isnull(#{areaCode}),true,t1.areacode like CONCAT(#{areaCode},'%')) and if(isnull(#{dateTime}),true,t1.createTime like CONCAT(#{dateTime},'%'))")
    List<Agrinfo> getAgrinfos(@Param("type") String type,@Param("areaCode")String areaCode,@Param("dateTime")String dateTime);

}
